import os
from datetime import datetime, timezone
from time import timezone
from typing import Optional

from pydantic import BaseModel, field_serializer
from sqlalchemy import Column, Integer, SmallInteger, String, Text, DateTime, text, bindparam
from sqlalchemy.ext.declarative import declarative_base

from Logs.log_handle import get_log
from tools.db_model_tool import transactional, db_session

# ----------------------------- 初始化日志与模型基础 -----------------------------

# 获取日志记录器，文件名作为日志前缀
log = get_log(__file__.split(os.path.sep)[-1])

# 创建 SQLAlchemy 的基础模型类，所有 ORM 模型都要继承这个类
Base = declarative_base()


# ----------------------------- 数据库模型 -----------------------------
class Testcase(Base):
    """
    测试用例数据库模型，对应表 tb_test_case
    """

    __tablename__ = "tb_test_case"

    case_id = Column(Integer, primary_key=True, index=True)
    feature = Column(String(255), nullable=True)
    title = Column(String(255), nullable=True)
    url = Column(Text, nullable=True)
    header = Column(Text, nullable=True)
    method = Column(String(255), nullable=True)
    pk = Column(String(255), nullable=True)
    data = Column(Text, nullable=True)
    file = Column(String(255), nullable=True)
    extract = Column(String(255), nullable=True)
    validate = Column(String(255), nullable=True)
    priority = Column(String(255), nullable=True)
    is_del = Column(SmallInteger, default=1)
    created_at = Column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=True,
                        comment='创建时间')
    updated_at = Column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc),
                        onupdate=lambda: datetime.now(timezone.utc), nullable=True, comment='更新时间')


# ----------------------------- 请求体模型 DTO -----------------------------
class TestcaseCreate(BaseModel):
    """
    用于创建测试用例时的请求体模型（Data Transfer Object）
    """
    feature: Optional[str] = None
    title: Optional[str] = None
    url: Optional[str] = None
    header: Optional[str] = None
    method: Optional[str] = None
    pk: Optional[str] = None
    data: Optional[str] = None
    file: Optional[str] = None
    extract: Optional[str] = None
    validate: Optional[str] = None
    priority: Optional[str] = None

    class Config:
        # 支持从 ORM 对象中读取数据
        from_attributes = True  # Pydantic v2 使用 from_attributes 替代 from_orm


# ----------------------------- 响应体模型 VO -----------------------------
class TestcaseResponse(BaseModel):
    """
    测试用例响应体模型（用于返回完整数据）
    """
    case_id: int
    feature: Optional[str] = None
    title: Optional[str] = None
    url: Optional[str] = None
    header: Optional[str] = None
    method: Optional[str] = None
    pk: Optional[str] = None
    data: Optional[str] = None
    file: Optional[str] = None
    extract: Optional[str] = None
    validate: Optional[str] = None
    priority: Optional[str] = None
    created_at: Optional[datetime] = None
    updated_at: Optional[datetime] = None

    @field_serializer("created_at", "updated_at", check_fields=False)
    def serialize_datetime(self, dt: Optional[datetime]) -> Optional[str]:
        return dt.strftime("%Y-%m-%d %H:%M:%S") if dt else None

    # 支持从 ORM 模型转换而来
    class Config:
        from_attributes = True


class TestcaseResponseCases(BaseModel):
    """
    简化版响应体模型（无时间字段，用于批量查询）
    """
    case_id: int
    feature: Optional[str] = None
    title: Optional[str] = None
    url: Optional[str] = None
    header: Optional[str] = None
    method: Optional[str] = None
    pk: Optional[str] = None
    data: Optional[str] = None
    file: Optional[str] = None
    extract: Optional[str] = None
    validate: Optional[str] = None
    priority: Optional[str] = None

    class Config:
        from_attributes = True


# ----------------------------- CURD 操作函数 -----------------------------

# ---------------- 创建（INSERT） ----------------
@transactional
def create_testcase_in_db(testcase: TestcaseCreate, db) -> dict:
    # 1. 获取 UTC 现在时间

    sql = text("""
        INSERT INTO tb_test_case
          (feature, title, url, header, method, pk, data, file,
           extract, validate, priority)
        VALUES
          (:feature, :title, :url, :header, :method, :pk, :data, :file,
           :extract, :validate, :priority)
    """)
    params = testcase.dict()

    res = db.execute(sql, params)
    case_id = res.lastrowid

    log.info(f"创建测试用例成功，ID={case_id}，数据={params}")
    return {"message": "添加数据成功", "id": case_id}


# ---------------- 分页查询（SELECT） ----------------
@db_session
def select_testcase_in_db(page: int, size: int, testcase: TestcaseCreate, db) -> dict:
    filters = []
    params = {}
    for field, value in testcase.dict().items():
        if value:
            filters.append(f"{field} LIKE :{field}")
            params[field] = f"%{value}%"
    where = " AND ".join(filters)
    if where:
        where = "WHERE is_del=1 AND " + where
    else:
        where = "WHERE is_del=1"

    count_sql = text(f"SELECT COUNT(*) AS cnt FROM tb_test_case {where}")
    total = db.execute(count_sql, params).scalar()

    data_sql = text(f"""
        SELECT * FROM tb_test_case
        {where}
        ORDER BY case_id ASC
        LIMIT :offset, :limit
    """)
    params.update({"offset": (page - 1) * size, "limit": size})
    rows = db.execute(data_sql, params).mappings().all()

    items = [TestcaseResponse.from_orm(row) for row in rows]
    log.info(f"查询测试用例：分页 {page}，每页 {size}，共 {total} 条")
    return {"total": total, "page": page, "size": size, "data": items}


# ---------------- 按 ID 查询 ----------------
@db_session
def select_testcases_by_ids(case_ids: list[int], db) -> list[TestcaseResponse]:
    if not case_ids:
        raise ValueError("case_ids 不能为空")
    sql = text("SELECT * FROM tb_test_case WHERE case_id IN :ids AND is_del=1")
    rows = db.execute(sql, {"ids": tuple(case_ids)}).mappings().all()
    items = [TestcaseResponse.from_orm(row) for row in rows]
    log.info(f"按 ID 查询测试用例，ID={case_ids}，返回 {len(items)} 条")
    return items


# ---------------- 逻辑删除（UPDATE is_del=0） ----------------
@transactional
def del_testcases_id_and_ids(case_ids: list[int], db) -> dict:
    if not case_ids:
        return {"message": "未提供 case_ids"}
    sql = text("""
        UPDATE tb_test_case
        SET is_del = 0, updated_at = CURRENT_TIMESTAMP
        WHERE case_id IN :ids AND is_del = 1
    """).bindparams(bindparam("ids", expanding=True))

    res = db.execute(sql, {"ids": case_ids})
    count = res.rowcount
    log.info(f"逻辑删除 {count} 条测试用例，ID 列表：{case_ids}")
    return {
        "message": "删除成功",
        "deleted": count,
        "case_ids": case_ids
    }


# ---------------- 更新（UPDATE） ----------------
@transactional
def update_testcases_case_in_db(testcase: TestcaseResponse, db) -> dict:
    data = testcase.dict(exclude_unset=True)
    case_id = data.pop("case_id", None)
    if not case_id:
        raise ValueError("缺少 case_id")
    # 从 dict 中移除 None 和时间字段
    data.pop("created_at", None)
    data.pop("updated_at", None)

    set_clause = ", ".join([f"{k} = :{k}" for k in data])
    sql = text(f"""
        UPDATE tb_test_case
        SET {set_clause}, updated_at = CURRENT_TIMESTAMP
        WHERE case_id = :case_id AND is_del = 1
    """)
    params = {**data, "case_id": case_id}
    res = db.execute(sql, params)
    if res.rowcount == 0:
        log.warning(f"未找到要更新的测试用例，ID={case_id}")
        return {"message": "未找到对应的测试用例"}
    log.info(f"更新测试用例成功，ID={case_id}，更新字段={data}")
    return {"message": "更新成功"}
